When a database server instance receives a database query from a database client, the database server instance creates a query execution plan to answer the database query. Frequently, the query execution plan will require one or more scan operations, such as table scan operations, to read rows from a specified table in a SQL SELECT or JOIN statement. Other examples of data scan operations include index scans and transaction journal scans.
To accelerate scan performance, a database system may store frequently accessed data in a buffer cache. During execution of an execution plan that requires a data scan, dedicated parallel query slaves can be used to scan the cached data. However, this approach can lead to inefficient use of processing resources, particularly when the database system is a multi-node, multi-instance database. Since the dedicated query slaves are only allocated at the database instance where the corresponding database query is initiated, optimal load balancing of the data scan operations over multiple database instances may be difficult to achieve. Additionally, the dedicated query slaves reserve and block processing resources that may be better directed to servicing other database queries that become more important or higher priority over time.
In multi-node database systems, a table scan operation may be distributed among different nodes when the table is partitioned. Specifically, the task of scanning the entire table may be split into task for scanning the individual partitions. Each partition-scan operation may be assigned to a distinct node, so long as the assigned node has access to the disk on which its assigned partition resides. Techniques for assigning partitions to distinct nodes, and distributing work accordingly, are described for example in U.S. Pat. No. 6,711,571. While such techniques make use of more of the resources available in a multi-node system, each node still has to read from disk the data from its assigned partition. Disk reads are significantly slower than accessing the data from volatile memory.
In dual-format database systems, optimizing scan operations can become even more complicated. Dual-format database systems typically include copies of all data items in a database in one format, and copies of a subset of the data items of the database in another format. For example, U.S. patent application Ser. No. 14/337,179, which is incorporated herein by reference, describes a system in which copies of all data items are stored in row-major tables, and copies of a subset of the data items are also stored in column-major in-memory compression units (IMCUs).
Such in-memory compression units can be employed to provide a dramatic performance boost for many database workloads. For example, in a system that stores heavily-read data items in an in-memory column-major (IMC) store, during query execution, those data items may be retrieved directly from the IMC store rather than from the row-major store, resulting in faster retrieval operations. Optimizing scans in multi-instance dual-format systems is particularly complicated by the fact that any given data item may reside in an IMC store, in buffer cache, and on disk. Further, when in an IMC store, the IMC store may be on a different node than the node that is running the database server instance that receives the query that requires the scans.
Based on the foregoing, there is a need for an efficient way to service data scan operations for dual-format or in-memory databases, particularly for multi-instance in-memory databases.
The approaches described in this section are approaches that could be pursued, but not necessarily approaches that have been previously conceived or pursued. Therefore, unless otherwise indicated, it should not be assumed that any of the approaches described in this section qualify as prior art merely by virtue of their inclusion in this section.